package databaseLayer;

import java.util.List;
import model.Purchase;


import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import util.HibernateUtil;

public class Purchase_db {

    public List<Purchase> selectUnpaidByUsername(String memberUsername) {
        Session session = null;
        Transaction tx = null;
        try {
            session = HibernateUtil.getThreadLocalSession();
            tx = session.beginTransaction();
            Query query = session.createQuery("from Purchase where memberUsername=:memberUsername and remark='Unpaid'");
            query.setString("memberUsername", memberUsername);
            List<Purchase> result = query.list();
            return result;
        } catch (Exception e) {
            tx.rollback();
        } finally {
            if (session != null) {
                HibernateUtil.closeSession();
            }
        }
        return null;
    }

    public List<Purchase> selectPurchasesByUsername(String memberUsername) {
        Session session = null;
        Transaction tx = null;
        try {
            session = HibernateUtil.getThreadLocalSession();
            tx = session.beginTransaction();
            Query query = session.createQuery("from Purchase where memberUsername=:memberUsername");
            query.setString("memberUsername", memberUsername);
            List<Purchase> result = query.list();
            return result;
        } catch (Exception e) {
            tx.rollback();
        } finally {
            if (session != null) {
                HibernateUtil.closeSession();
            }
        }
        return null;
    }

    public List<Purchase> selectPurchasesByRemark(String remark) {
        Session session = null;
        Transaction tx = null;
        try {
            session = HibernateUtil.getThreadLocalSession();
            tx = session.beginTransaction();
            Query query = session.createQuery("from Purchase where remark=:remark");
            query.setString("remark", remark);
            List<Purchase> result = query.list();
            return result;
        } catch (Exception e) {
            tx.rollback();
        } finally {
            if (session != null) {
                HibernateUtil.closeSession();
            }
        }
        return null;
    }

    public Integer selectSalesByMovieName(String movieName) {
        Session session = null;
        Transaction tx = null;
        try {
            session = HibernateUtil.getThreadLocalSession();
            tx = session.beginTransaction();
            Query query = session.createQuery("select distinct count(*) "
                    + "from Purchase p, MovieSection ms "
                    + "where p.purchaseId.movieSectionId=ms.id "
                    + "and ms.movieName=:movieName");
            query.setString("movieName", movieName);
            Long LongResult = (Long) query.list().get(0);
            int intResult = LongResult.intValue();
            return (Integer) intResult;
        } catch (Exception e) {
            tx.rollback();
        } finally {
            if (session != null) {
                HibernateUtil.closeSession();
            }
        }
        return null;
    }

    public Integer selectSalesByHouse(String houseId) {
        Session session = null;
        Transaction tx = null;
        try {
            session = HibernateUtil.getThreadLocalSession();
            tx = session.beginTransaction();
            Query query = session.createQuery("select distinct count(*) "
                    + "from Purchase p, MovieSection ms "
                    + "where p.purchaseId.movieSectionId=ms.id "
                    + "and ms.houseId=:houseId");
            query.setString("houseId", houseId);
            Long LongResult = (Long) query.list().get(0);
            int intResult = LongResult.intValue();
            return (Integer) intResult;
        } catch (Exception e) {
            tx.rollback();
        } finally {
            if (session != null) {
                HibernateUtil.closeSession();
            }
        }
        return null;
    }

    public List<String> selectOccupiedSeats(String movieSectionId) {
        Session session = null;
        Transaction tx = null;
        try {
            session = HibernateUtil.getThreadLocalSession();
            tx = session.beginTransaction();
            Query query = session.createQuery("select pur.purchaseId.seatNo from Purchase as pur where movieSectionId=:movieSectionId");
            query.setString("movieSectionId", movieSectionId);
            List<String> result = query.list();
            return result;
        } catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        } finally {
            if (session != null) {
                HibernateUtil.closeSession();
            }
        }
        return null;
    }

    private Purchase_db() {
    }
    private static Purchase_db instance;

    public static Purchase_db getInstance() {
        if (instance == null) {
            instance = new Purchase_db();
        }

        return instance;
    }
}
